package com.jmcgg.freesky.common.util;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

import com.jmcgg.freesky.common.db.DBConnBean;

/**
 * Title: FreeSky Common Java Library <br>
 * Description: FreeSky Common Java Library <br>
 * Copyright: Copyright (c) 2005~2009 www.afss.cn <br>
 * Company: Amei's FreeSky Studio <br>
 * 
 * @author <b>Jacky Chou</b> (AFSS [Amei's FreeSky Studio])
 * @version 1.2.1, 2008-08-05, Clean and optimize the source code. <br>
 *          1.2.0, 2008-02-23, Upgrade freesky.jar to 1.2.0 with Struts 2 <br>
 *          1.0.0, 2006-05-30, Initial Version
 */

public class SequenceUtil extends FreeskyUtil {
	/**
	 * Log4J Logger for this class
	 */
	private static final Logger logger = Logger.getLogger(SequenceUtil.class);

	// Get next sequence number according to table name
	private static String sqlSelectSequence = "SELECT SEQUENCE FROM FS_SYS_SEQUENCE WHERE TABLE_NAME = ?";
	// Add 1 to the current sequence number after get sequence number
	private static String sqlUpdateSequence = "UPDATE FS_SYS_SEQUENCE SET SEQUENCE = ? WHERE TABLE_NAME = ?";
	// Insert first record if there is no existing record and set initial number
	// 2
	private static String sqlInsertSequence = "INSERT INTO FS_SYS_SEQUENCE(TABLE_NAME,SEQUENCE) VALUES(?,?)";

	public SequenceUtil() {
	}

	/**
	 * Get next sequence number according to table name (get only one once)
	 * 
	 * @param tblName
	 *            Table Name
	 * @return
	 */
	public synchronized static int getSequence(DBConnBean conn, String tblName) {
		return getSequence(conn, tblName, 1);
	}

	/**
	 * Get next sequence number according to table name
	 * 
	 * @param tblName
	 *            Table Name
	 * @param num
	 *            the number of sequence numbers
	 * @return tmpJoint sequence number if successful; 0 if fails.
	 */
	public synchronized static int getSequence(DBConnBean conn, String tblName,
			int num) {
		int tmpSequence = 0;
		try {
			PreparedStatement ps = conn.prepareStatement(sqlSelectSequence);
			ps.setString(1, tblName); // Set table name
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				tmpSequence = rs.getInt("SEQUENCE");
			}
			rs.close();
			ps.close();
			if (tmpSequence != 0) {
				ps = conn.prepareStatement(sqlUpdateSequence);
				ps.setInt(1, tmpSequence + num);
				ps.setString(2, tblName);
				ps.executeUpdate();
				ps.getConnection().commit();
				ps.close();
			}
			// if not existed, insert a new one
			else {
				ps = conn.prepareStatement(sqlInsertSequence);
				ps.setString(1, tblName);
				ps.setInt(2, 1 + num);
				ps.executeUpdate();
				ps.getConnection().commit();
				ps.close();
				tmpSequence = 1;
			}
		} catch (SQLException e) {
			logger.error(
					"getSequence(DBConnBean, String, int) - String tblName="
							+ tblName, e);

			tmpSequence = 0; // 如果失败 返回 0
		} finally {
			if (conn != null) {
				conn.close();
				conn = null;
			}
		}
		return tmpSequence;
	}
}
